import json

import uuid

import pymysql

import datetime

from pprint import pprint

from flask import Flask, request

class DateEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')

        elif isinstance(obj, datetime.date):
            return obj.strftime("%Y-%m-%d")

        else:
            return json.JSONEncoder.default(self, obj)

class Connect_Mysql:

    def __init__(self):

        self.conn = pymysql.connect(
            host = '140.143.206.157',
            port = 3306,
            user = 'root',
            password = '123456',
            db = 'mmwl',
            charset = 'utf8',
        )

        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

        self.LID_SQL = 'SELECT LAST_INSERT_ID()'

    def close(self):

        self.cursor.close()

        self.conn.close()

    def Select_info_all(self,sql,args = None):

        if args == None:

            self.cursor.execute(sql)

        else:

            self.cursor.execute(sql,args)

        Info = self.cursor.fetchall()

        if Info:

            return {
                'status': 1,
                'errorCode': '',
                'errorMsg': '',
                'resultBody': json.dumps(Info, cls=DateEncoder),
            }

        else:

            return {
                'status': -1,
                'errorCode': 'ValueError',
                'errorMsg':f'请检查你的args:{args}错误',
                'resultBody':None,
            }

    def Select_info_one(self,sql,args = None):

        if args == None:

            self.cursor.execute(sql)

        else:

            self.cursor.execute(sql,args)

        Info = self.cursor.fetchone()

        if Info:

            return {
                'status': 1,
                'errorCode': '',
                'errorMsg': '',
                'resultBody': json.dumps(Info, cls=DateEncoder),
            }

        else:

            return {
                'status': -1,
                'errorCode': 'ValueError',
                'errorMsg':f'请检查你的args:{args}错误',
                'resultBody':None,
            }


    def Insert_info(self,sql,args = None):

        if args == None:

            self.cursor.execute(sql)

        else:

            self.cursor.execute(sql,args)

        ID = self.Select_info_one(self.LID_SQL)['resultBody']

        self.conn.commit()

        return ID

    def Update_info(self, sql, args = None):

        if args == None:
    
            self.cursor.execute(sql)

        else:
    
            self.cursor.execute(sql, args)

        self.conn.commit()

        return {
                'status': 1,
                'errorCode': '',
                'errorMsg': '',
                'resultBody': json.dumps("更改成功"),
            }


    def info(self,page,limit):

        page = request.args.get('page')

        limit = request.args.get('limit')

        if page == None:

            page = '1'

        if limit == None:

            limit = '20'

        if page.isdecimal() and limit.isdecimal():

            page = int(page)
            limit = int(limit)

        else:

            return {
                'status': -1,
                'errorCode': 'ValueError',
                'errorMsg':f'请检查page和limit参数是否合法,page:{page},limit:{limit}',
                'resultBody':''
            }

        sql = 'select company_name,product_name,need,linkman,party from company limit %s,%s'

        result_info = self.Select_info_all(sql, ((page-1)*limit, limit))

        result_info ={

            'status': 1,
            'errorCode': '',
            'errorMsg': '',
            'resultBody': result_info
        }

        return json.dumps(result_info,cls=DateEncoder)

        
    def login(self,phone,pass_word):

        # print(pass_word)
# 
        sql = 'select * from company where phone = %s and pass_word = %s'

        self.cursor.execute(sql,(phone,pass_word))

        Info = self.cursor.fetchone()

        # print(Info)

        if Info == None:

            return {
                    'status': -1,
                    'errorCode': '6',
                    'errorMsg': '此电话没有注册信息',
                    'resultBody': None
                }

        else:

            token = str(uuid.uuid1().int)

            Info['token'] = token

            company_id = Info['id']

            sql = 'insert into token (token,company_id) values (%s,%s)'

            self.cursor.execute(sql,(token,company_id))

            self.conn.commit()

            return  {
                    'status': 1,
                    'errorCode': '',
                    'errorMsg': '',
                    'resultBody': {'info': Info, 'token': token}
                }


    def insert(self):

        sql = '''INSERT INTO company

        (company_name, product_name, linkman, need, phone, party, pass_word)

        VALUES

        (%s,%s,%s,%s,%s,%s,%s);  

        '''

        INFO = request.form

        info = (
            INFO.get('company_name'),
            INFO.get('product_name'),
            INFO.get('linkman'),
            INFO.get('need'),
            INFO.get('phone'),
            INFO.get('party'),
            INFO.get('pass_word'),
        )

        if all(info):

            try:

                self.Insert_info(sql, info)

            except pymysql.err.DataError as e:

                print(dir(e))

                print(e.args)

                return str(e)

            else:

                return {
                    'status': 1,
                    'errorCode': '',
                    'errorMsg': '',
                    'resultBody': json.dumps("入库成功"),
                }

        else:

            return {
                    'status': -1,
                    'errorCode': 'ValueError',
                    'errorMsg':'请检查提交的数据',
                    'resultBody': None,
                }

    def updat(self,ID):

        sql = 'UPDATE company SET company_name=%s, product_name=%s, linkman=%s, need=%a, phone=%s, party=%s, pass_word=%s WHERE id=%s'

        INFO = request.form

        info = (
            INFO.get('company_name'),
            INFO.get('product_name'),
            INFO.get('linkman'),
            INFO.get('need'),
            INFO.get('phone'),
            INFO.get('party'),
            INFO.get('pass_word'),
            ID
        )

        if all(info):

            try:

                self.Update_info(sql, info)

            except pymysql.err.DataError as e:

                print(dir(e))

                print(e.args)

                return str(e)

            else:

                return {
                    'status': 1,
                    'errorCode': '',
                    'errorMsg': '',
                    'resultBody': json.dumps("更改成功"),
                }

        else:

            return {
                    'status': -1,
                    'errorCode': 'ValueError',
                    'errorMsg':'请检查提交的数据',
                    'resultBody':None,
                }


    def return_consumption_all(self,page,limit,args= None):

        if args == None:

            if page == None:

                page = '1'

            if limit == None:

                limit = '20'

            if page.isdecimal() and limit.isdecimal():

                page = int(page)
                limit = int(limit)

            else:

                return {
                    'status': -1,
                    'errorCode': 'ValueError',
                    'errorMsg':f'请检查page和limit参数是否合法,page:{page},limit:{limit}',
                    'resultBody':''
                }

            consumption_sql = 'select company_id,consumption_url,consumption_surplus,creat_time from consumption limit %s,%s'

            self.cursor.execute(consumption_sql,((page-1)*limit, limit))

        else:

            consumption_sql = 'select company_id,consumption_url,consumption_surplus,creat_time from consumption where id = %s'

            self.cursor.execute(consumption_sql,args)

        info = self.cursor.fetchall()

        if info:
    
            return info

        else:
            
            None

    def return_company_all(self,page,limit,args= None):

        if args == None:

            if page == None:

                page = '1'

            if limit == None:

                limit = '20'

            if page.isdecimal() and limit.isdecimal():

                page = int(page)
                limit = int(limit)

            else:

                return {
                    'status': -1,
                    'errorCode': 'ValueError',
                    'errorMsg':f'请检查page和limit参数是否合法,page:{page},limit:{limit}',
                    'resultBody':''
                }

            company_sql = 'select id,company_name,product_name,need,linkman,party from company limit %s,%s'

            self.cursor.execute(company_sql,((page-1)*limit, limit))

            

        else:

            company_sql = 'select id,company_name,product_name,need,linkman,party from company where id = %s'
            
            self.cursor.execute(company_sql,args)

        info = self.cursor.fetchall()

        if info:

            return info

        else:
            
            return None

    def hebingxinxi(self,page,limit,args = None):

        if args == None:

            company_info = self.return_company_all(page,limit)

            consumption_info = self.return_consumption_all(page,limit)

            info = []

            for id in company_info: 

                for company_id in consumption_info:

                    if company_id['company_id'] == id['id']:

                        company_id['company_name'] = id['company_name']
                        company_id['product_name'] = id['product_name']
                        company_id['need'] = id['need']
                        company_id['linkman'] = id['linkman']
                        company_id['party'] = id['party']

                        info.append(company_id)

            return {
                    'status': 1,
                    'errorCode': '',
                    'errorMsg':'',
                    'resultBody':json.dumps(info,cls=DateEncoder)
                }

        else:

            page = 1

            limit = 1

            company_info = self.return_company_all(page,limit,args)

            consumption_info = self.return_consumption_all(page,limit,args)

            if consumption_info == None:

                return {'status': -1,
                    'errorCode': '',
                    'errorMsg':'无此ID对应的数据',
                    'resultBody':None,
                }

            else:

                sql = 'select phone from company where id = %s'

                self.cursor.execute(sql,(args))

                info = self.cursor.fetchone()

                for id in company_info:

                    id = id 

                for company_id in consumption_info:

                    company_id = company_id

                if company_id['company_id'] == id['id']:

                    company_id['company_name'] = id['company_name']
                    company_id['product_name'] = id['product_name']
                    company_id['linkman'] = id['linkman']
                    company_id['party'] = id['party']
                    company_id['phone'] = info['phone']

                return {
                    'status': 1,
                    'errorCode': '',
                    'errorMsg':'',
                    'resultBody':json.dumps(company_id,cls=DateEncoder)
                }

    


if __name__ == "__main__":

    # Insert_info_sql = '''INSERT INTO company

    #     (company_name, linkman, need, phone, party, residue_degree, pass_word)

    #     VALUES

    #     (%s,%s,%s,%s,%s,%s,%s);    

    #     '''
    # pprint(Connect_Mysql().Insert_info(Insert_info_sql,('1','1','1',1,1,1,'111')))


    # Select_info_all_sql = 'select company_name,product_name,need,linkman,party from company limit 10'

    # pprint(Connect_Mysql().Select_info_all(Select_info_all_sql))


    # Select_info_one = 'SELECT company_name,product_name,need,linkman,party FROM company WHERE id = %s'
    
    # pprint(Connect_Mysql().Select_info_one(Select_info_one,'1'))


    # Delete_sql = 'DELETE FROM company WHERE id = %s'

    # pprint(Connect_Mysql().Delete_info(Delete_sql,410))


    # Update_info_sql = 'UPDATE company SET company_name=%s WHERE id=%s'

    # print(Connect_Mysql().Update_info(Update_info_sql,('111',446)))

    # print(Connect_Mysql().return_consumption_all('1','10',3))

    # print(Connect_Mysql().return_company_all('1','10',3))

    print(Connect_Mysql().hebingxinxi('1','10',1))

    pass


    